﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using DataGeneration.Domain.CustomProcessors;
using DataGeneration.Domain.DataAccessLayer;
using DataGeneration.Domain.Helpers;
using DataGeneration.Domain.Logging;

namespace DataGeneration.Domain.Logic
{
    /// <summary>
    /// Defines the type PatternProcessor
    /// </summary>
    public class PatternProcessor : PatternProcessorBase
    {
        private static string[] SplitString(IList<string> content, string key)
        {
            string[] entries = null;
            foreach (string s in content)
            {
                string[] temp = s.Split(",".ToCharArray());
                if (temp[0].Trim().ToLower().Equals(key.ToLower().Trim()))
                {
                    entries = temp;
                }
            }
            return entries;
        }

        public void ProcessData()
        {
            XDocument xdocument =
                XDocument.Load(@"C:\workspace\phoenix-data-generator\DataGeneration\Domain\Resources\temp.xml");
            IEnumerable<XElement> dumpPattern = from pattern in xdocument.Descendants("Dump") select pattern;
            IList<string> content = FileHelper.ReadFileContent(@"C:\Users\easemota\Desktop\Offices.csv");

            int counter = 1;
            string filename = @"C:\workspace\phoenix-sit\DataPatterns\OfficePattern.xml";
            FileHelper.CreateFile(filename);
            //string.Format(@"C:\workspace\phoenix-sit\DataPatterns\{0}.xml", filename));

            var sb = new StringBuilder();

            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
            sb.AppendLine();
            sb.Append("<DataLoad cleanDB=\"false\">");
            sb.AppendLine();
            sb.Append(
                "<Pattern patternName=\"Pattern1\" quantity=\"1\" insertIntoDb=\"true\"  generateCSV=\"false\"  persistSQL=\"true\">");
            sb.AppendLine();


            foreach (XElement datap in dumpPattern)
            {
                XElement partyEntity = (from p in datap.Descendants()
                                        where p.ContainsAttributeWithValue("tableName", "PARTIES")
                                        select p).First();
                XAttribute ads = (from we in partyEntity.Attributes() where we.Name == "entityName" select we).First();

                string key = ads.Value.Replace("PARTIES", "");
                string[] entries = SplitString(content, key);

                #region MyRegion

                //StringBuilder sb = new StringBuilder();
                //sb.Append(datap.ToString());

                //sb.AppendFormat()

                //sb.Replace("propertyName=\"PARTY_KEY\" type=\"integer\" key=\"true\"  references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />",
                //                string.Format("propertyName=\"PARTY_KEY\"  type=\"integer\" key=\"true\"  references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />", counter))
                //.Replace("references=\"PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />",
                //                                string.Format("references=\"PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />", counter))
                /*
                sb.Replace("actualValue=\"[Description]\"", string.Format("actualValue=\"'{0}'\"", entries[5]))
                .Replace("actualValue=\"[POST_Code]\"", string.Format("actualValue=\"'{0}'\"",  entries[1]))
                .Replace("actualValue=\"[PAON]\"", string.Format("actualValue=\"'{0}'\"",  entries[12]))
                .Replace("actualValue=\"[Str_desc]\"", string.Format("actualValue=\"'{0}'\"",  entries[13]))
                .Replace("actualValue=\"[Region]\"", string.Format("actualValue=\"'{0}'\"",  entries[3]))
                .Replace("actualValue=\"[Town]\"", string.Format("actualValue=\"'{0}'\"",  entries[9]))
                .Replace("actualValue=\"[Phone_num]\"", string.Format("actualValue=\"'{0}'\"",  entries[6]))
                .Replace("actualValue=\"[Fax_num]\"", string.Format("actualValue=\"'{0}'\"",  entries[7]))
                .Replace("actualValue=\"[Via_gro1_e_mail]\"", string.Format("actualValue=\"'{0}'\"", entries[10]))
                ;
                */

                #endregion

                sb.AppendFormat("<Entity entityName=\"{0}PARTIES\" tableName=\"PARTIES\">", key);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                    counter);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_ROLE_KEY\" type=\"integer\" key=\"false\" references=\"PARTY_ROLES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"3\" />");
                sb.AppendLine();
                sb.AppendFormat("</Entity>");
                sb.AppendLine();
                sb.AppendFormat("<Entity entityName=\"{0}COPFS_OFFICES\" tableName=\"COPFS_OFFICES\">", key);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"true\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                    counter, key);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"OFFICE_CODE\" type=\"string\" key=\"false\" references=\"\" hint=\"unique-2\" nullable=\"false\" default=\"\" actualValue=\"'{0}'\" />",
                    key);
                sb.AppendLine();
                sb.AppendLine();
                sb.AppendLine();
                sb.AppendFormat("</Entity>");
                sb.AppendLine();
                sb.AppendLine();
                sb.AppendLine();
                sb.AppendFormat("<Entity entityName=\"PARTY_NAMES\" tableName=\"PARTY_NAMES\">");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_NAME_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"false\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                    counter, key);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_NAME_TYPE_KEY\" type=\"integer\" key=\"false\" references=\"PARTY_NAME_TYPES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"3\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"DATE_CREATED\" type=\"DateTime\" key=\"false\" references=\"\" hint=\"today\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PREFERRED_FLAG\" type=\"string\" key=\"false\" references=\"\" hint=\"max-1 \" nullable=\"true\" default=\"\" actualValue=\"'Y'\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"MAIN_NAME\" type=\"string\" key=\"false\" references=\"\" hint=\"surname\" nullable=\"false\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[5].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat("</Entity>");
                sb.AppendLine();
                sb.AppendFormat("<Entity entityName=\"ADDRESSES\" tableName=\"ADDRESSES\">");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"ADDRESS_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"false\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                    counter, key);
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"ADDRESS_TYPE_KEY\" type=\"integer\" key=\"false\" references=\"ADDRESS_TYPES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"5\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"DATE_CREATED\" type=\"DateTime\" key=\"false\" references=\"\" hint=\"today\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PREFERRED_POST_FLAG\" type=\"string\" key=\"false\" references=\"\" hint=\"max-1 \" nullable=\"true\" default=\"\" actualValue=\"'Y'\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"ADDRESS_LINE_1\" type=\"string\" key=\"false\" references=\"\" hint=\"addressline1-50\" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[13].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"POST_CODE\" type=\"string\" key=\"false\" references=\"\" hint=\"postcode\" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[1].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"COUNTRY\" type=\"string\" key=\"false\" references=\"\" hint=\"country\" nullable=\"true\" default=\"\" actualValue=\"'UK'\" />");
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"PAON\" type=\"string\" key=\"false\" references=\"\" hint=\"max-50 \" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[12].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"STREET\" type=\"string\" key=\"false\" references=\"\" hint=\"max-50 \" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[13].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"LOCALITY\" type=\"string\" key=\"false\" references=\"\" hint=\"max-50 \" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[3].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"TOWN\" type=\"string\" key=\"false\" references=\"\" hint=\"town\" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[9].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat(
                    "<Property propertyName=\"POST_TOWN\" type=\"string\" key=\"false\" references=\"\" hint=\"max-30 \" nullable=\"true\" default=\"\" actualValue=\"'{0}'\" />",
                    entries[9].Trim().Replace("&", "and").Replace("'", "''"));
                sb.AppendLine();
                sb.AppendFormat("</Entity>");
                sb.AppendLine();

                if (!string.IsNullOrWhiteSpace(entries[6]))
                {
                    sb.AppendFormat("<Entity entityName=\"TELEPHONES\" tableName=\"TELEPHONES\">");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"false\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                        counter, key);
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_TYPE_KEY\" type=\"string\" key=\"false\" references=\"TELEPHONE_TYPES\" hint=\"max-50 \" nullable=\"false\" default=\"\" actualValue=\"2\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"DATE_CREATED\" type=\"DateTime\" key=\"false\" references=\"\" hint=\"today\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PREFERRED_FLAG\" type=\"string\" key=\"false\" references=\"\" hint=\"max-1 \" nullable=\"true\" default=\"\" actualValue=\"'Y'\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"DESCRIPTION\" type=\"string\" key=\"false\" references=\"\" hint=\"max-50 \" nullable=\"false\" default=\"\" actualValue=\"'WORK LAND LINE'\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_NUMBER\" type=\"string\" key=\"false\" references=\"\" hint=\"telephone\" nullable=\"false\" default=\"\" actualValue=\"'{0}'\" />",
                        entries[6].Trim().Replace("&", "and").Replace("'", "''"));
                    sb.AppendLine();
                    sb.AppendFormat("</Entity>");
                    sb.AppendLine();
                }

                if (!string.IsNullOrWhiteSpace(entries[7]))
                {
                    sb.AppendFormat("<Entity entityName=\"TELEPHONES2\" tableName=\"TELEPHONES\">");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"false\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                        counter, key);
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_TYPE_KEY\" type=\"string\" key=\"false\" references=\"TELEPHONE_TYPES\" hint=\"max-50 \" nullable=\"false\" default=\"\" actualValue=\"2\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"DATE_CREATED\" type=\"DateTime\" key=\"false\" references=\"\" hint=\"today\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PREFERRED_FLAG\" type=\"string\" key=\"false\" references=\"\" hint=\"max-1 \" nullable=\"true\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"DESCRIPTION\" type=\"string\" key=\"false\" references=\"\" hint=\"max-50 \" nullable=\"false\" default=\"\" actualValue=\"'WORK FAX MACHINE'\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"TELEPHONE_NUMBER\" type=\"string\" key=\"false\" references=\"\" hint=\"telephone\" nullable=\"false\" default=\"\" actualValue=\"'{0}'\" />",
                        entries[7].Trim().Replace("&", "and").Replace("'", "''"));
                    sb.AppendLine();
                    sb.AppendFormat("</Entity>");
                    sb.AppendLine();
                }

                if (!string.IsNullOrWhiteSpace(entries[10]))
                {
                    sb.AppendFormat("<Entity entityName=\"EMAILS\" tableName=\"EMAILS\">");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"EMAIL_KEY\" type=\"integer\" key=\"true\" references=\"\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PARTY_KEY\" type=\"integer\" key=\"false\" references=\"{1}PARTIES\" hint=\"\" nullable=\"false\" default=\"\" actualValue=\"{0}\" />",
                        counter, key);
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"DATE_CREATED\" type=\"DateTime\" key=\"false\" references=\"\" hint=\"today\" nullable=\"false\" default=\"\" actualValue=\"\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"PREFERRED_FLAG\" type=\"string\" key=\"false\" references=\"\" hint=\"max-1 \" nullable=\"true\" default=\"\" actualValue=\"'Y'\" />");
                    sb.AppendLine();
                    sb.AppendFormat(
                        "<Property propertyName=\"EMAIL_ADDRESS\" type=\"string\" key=\"false\" references=\"\" hint=\"emailaddress\" nullable=\"false\" default=\"\" actualValue=\"'{0}'\" />",
                        entries[10].Trim().Replace("&", "and").Replace("'", "''"));
                    sb.AppendLine();
                    sb.AppendFormat("</Entity>");
                    sb.AppendLine();
                }

                #region MyRegion

                //datap.ToString().Replace("actualValue=\"[Via_gro1_e_mail]\"",string.Format("actualValue=\"[Via_gro1_e_mail]\""))


                /*
                .Replace("",
                string.Format(""))
                 */
                //IEnumerable<XElement> partyEntity = from p in dataLoadPattern
                //                                    where p.ContainsAttributeWithValue("tableName", "PARTIES")
                //                                    select p;
                ////<Entity  entityName="ABPARTIES"  tableName="PARTIES">
                //foreach (XElement pe in partyEntity.First())
                //{
                //    //key="true"
                //    pe.
                //} 

                #endregion

                counter++;
            }
            sb.Append(" </Pattern>");
            sb.Append("</DataLoad>");

            FileHelper.SaveToFile(filename, sb.ToString());
        }


        public void UpdateSequences()
        {
            var sequenceTextList = FileHelper.ReadFileContent(ParameterizedAppSettings.SequenceDataFile);
            FileHelper.CreateFile(@"C:\SequenceUpdateSql.txt");
            if (ParameterizedAppSettings.DatabaseType.Equals(SupportedDatabase.Oracle))
            {
                using (DataAccess = InitializeDataAccess())
                {
                    try
                    {
                        foreach (var entry in sequenceTextList)
                        {
                            if (!string.IsNullOrWhiteSpace(entry))
                            {
                                var entryContent = entry.Split(";".ToCharArray(),
                                                                    StringSplitOptions.RemoveEmptyEntries);
                                if (entryContent.Length == 3)
                                {
                                    var lastTableId = DatabaseHelper.GetLastEntityIdFromDb(entryContent[1],
                                                                                           entryContent[2], DataAccess);
                                    var sqlStatement = string.Format("SELECT {0}.NEXTVAL FROM DUAL", entryContent[0]);
                                    DataAccess.InitializeCommand(sqlStatement, CommandType.Text);
                                    var lastSequenceNumber = DataAccess.ExecuteScalar<int>();
                                    if (lastTableId > lastSequenceNumber)
                                    {
                                        var sequenceUpdateSql =
                                            SqlServerInitializer.SequenceUpdateSql(lastSequenceNumber, lastTableId - 1,
                                                                                   entryContent[0]);
                                        FileHelper.SaveToFile(@"C:\SequenceUpdateSql.txt", sequenceUpdateSql);
                                        DataAccess.InitializeCommand(sequenceUpdateSql, CommandType.Text);
                                        DataAccess.ExecuteCommand();
                                    }
                                }
                            }
                        }
                        DataAccess.Transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        DataAccess.Transaction.Rollback();
                        Logger.WriteMessage(exception.Message);
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Generates the data load script from XML.
        /// </summary>
        public void GenerateDataLoadScriptFromXml()
        {
            IEnumerable<XElement> dataLoadPatterns = GetDataLoadPatterns();
            Logger.WriteMessage("Loaded data load patterns.");

            foreach (XElement dataLoadPattern in dataLoadPatterns)
            {
                string patternName = dataLoadPattern.ReadAttributeValue(Tokens.PatternName);

                Logger.WriteMessage(string.Format("Processing pattern {0}.", patternName));

                int quantity = Convert.ToInt32(dataLoadPattern.ReadAttributeValue(Tokens.Quantity));
                DateTime startTime = DateTime.Now;
                ProcessDataPatternLoad(dataLoadPattern, patternName, quantity);

                TimeSpan duration = DateTime.Now.Subtract(startTime);

                Logger.WriteMessage(string.Format("Processed pattern:{0} (Quantity={4}) in {1}hr {2}min {3}sec.",
                                                  patternName,
                                                  duration.Hours,
                                                  duration.Minutes,
                                                  duration.Seconds,
                                                  quantity));
            }
        }

        private void ProcessDataPatternLoad(XElement dataLoadPattern, string patternName, int quantity)
        {
            var patternEntities = dataLoadPattern.RetrieveChildElementsMatchingElementName(Tokens.Entity);
            var entitiesWithoutDependency = ExtractEntitiesWithoutDependency(patternEntities);
            Logger.WriteMessage("Extracted entities without dependencies.");

            var sortedEntitiesWithDependency = SortEntitiesByReference(ExtractEntitiesWithDependency(patternEntities));
            var insertIntoDb = Convert.ToBoolean(dataLoadPattern.ReadAttributeValue(Tokens.InsertIntoDb));
            var generateCsv = Convert.ToBoolean(dataLoadPattern.ReadAttributeValue(Tokens.GenerateCsv));
            var persistSql = Convert.ToBoolean(dataLoadPattern.ReadAttributeValue(Tokens.PersistSql));

            CreateFileRepositories(patternName);

            using (DataAccess = InitializeDataAccess())
            {
                try
                {
                    for (int counter = 0; counter < quantity; counter++)
                    {
                        var sqlBatchManager = new SqlBatchManager();
                        ProcessEntitiesWithoutDependency(entitiesWithoutDependency, patternName, persistSql, generateCsv,
                                                         insertIntoDb, sqlBatchManager, dataLoadPattern, counter);
                        ProcessEntitiesWithDependency(sortedEntitiesWithDependency, patternName, persistSql, generateCsv,
                                                      insertIntoDb, sqlBatchManager, dataLoadPattern, counter);
                        sqlBatchManager.ExecuteBatch(DataAccess, patternName, counter, insertIntoDb);
                        double percentageCompleted = ((double) counter + 1)/quantity;

                        if (!ParameterizedAppSettings.SilentMode)
                        {
                            Console.Title = string.Format("Completed {0:0%} of pattern {1} data generation",
                                                          percentageCompleted, patternName);
                        }
                        Logger.RecordPercentageComplete(percentageCompleted*100);
                    }

                    DataAccess.Transaction.Commit();
                }
                catch (Exception exception)
                {
                    DataAccess.Transaction.Rollback();
                    Logger.WriteMessage(exception.Message);
                    throw;
                }
            }
        }

        private static IEnumerable<XElement> ExtractEntitiesWithDependency(IEnumerable<XElement> patternEntities)
        {
            return (from entity in patternEntities
                    let c = (ExtractForeignKeyNodes(entity)).Count()
                    where c != 0
                    select entity).ToList();
        }

        private void ProcessEntitiesWithoutDependency(IEnumerable<XElement> entitiesWithoutDependency,
                                                      string patternName, bool persistSql, bool generateCsv,
                                                      bool insertIntoDb, SqlBatchManager sqlBatchManager,
                                                      XElement patternEntities, int currentIteration)
        {
            foreach (XElement entity in entitiesWithoutDependency)
            {
                string entityname = entity.ReadAttributeValue(Tokens.EntityName);
                string tableName = entity.ReadAttributeValue(Tokens.TableName);
                string entityFilename = ParameterizedAppSettings.GenerateEntitySqlFilename(patternName, entityname);
                string entityKeyFilename = ParameterizedAppSettings.GenerateEntityKeyFilename(patternName, tableName);
                string entityCsvFilename = ParameterizedAppSettings.GenerateEntityCsvFilename(patternName, entityname);
                bool identityInsert = entity.ContainsAttributeWithValue(Tokens.IdentityInsert, "true");

                FileHelper.CreateFile(entityKeyFilename);
                //lets get the last id for this entity from the database
                ReadPreviousIdFromDatabase(entity, entityKeyFilename);

                var propertyNames = new StringBuilder();
                var propertyValues = new StringBuilder();
                DbInsertScriptForEntitiesWithoutDependencies(entity, propertyNames, propertyValues, entityKeyFilename,
                                                             tableName, patternName, patternEntities, currentIteration);
                WriteEntityDbInsertScriptToFile(tableName, entityFilename, propertyNames, propertyValues,
                                                entityCsvFilename, persistSql, generateCsv, insertIntoDb, identityInsert,
                                                sqlBatchManager);
            }
        }

        private void ProcessEntitiesWithDependency(IEnumerable<XElement> sortedEntitiesWithDependency,
                                                   string patternName, bool persistSql, bool generateCsv,
                                                   bool insertIntoDb, SqlBatchManager sqlBatchManager,
                                                   XElement patternEntities, int currentIteration)
        {
            foreach (XElement entity in sortedEntitiesWithDependency)
            {
                string entityname = entity.ReadAttributeValue(Tokens.EntityName);
                string tableName = entity.ReadAttributeValue(Tokens.TableName);
                string entityFilename = ParameterizedAppSettings.GenerateEntitySqlFilename(patternName, entityname);
                string entityKeyFilename = ParameterizedAppSettings.GenerateEntityKeyFilename(patternName, tableName);
                string entityCsvFilename = ParameterizedAppSettings.GenerateEntityCsvFilename(patternName, entityname);
                bool identityInsert = entity.ContainsAttributeWithValue(Tokens.IdentityInsert, "true");

                FileHelper.CreateFile(entityKeyFilename);
                //lets get the last id for this entity from the database
                ReadPreviousIdFromDatabase(entity, entityKeyFilename);

                var propertyNames = new StringBuilder();
                var propertyValues = new StringBuilder();
                DbInsertScriptForEntitiesWithDependencies(entity, propertyNames, propertyValues, entityKeyFilename,
                                                          patternName, patternEntities, currentIteration);
                WriteEntityDbInsertScriptToFile(tableName, entityFilename, propertyNames, propertyValues,
                                                entityCsvFilename, persistSql, generateCsv, insertIntoDb, identityInsert,
                                                sqlBatchManager);
            }
        }

        private void ReadPreviousIdFromDatabase(XElement entity, string entityKeyFilename)
        {
            IEnumerable<XElement> p = from t in entity.Descendants()
                                      where t.ContainsAttributeWithValue(Tokens.Key, "true")
                                      select t;

            if (p.Count() > 0)
            {
                int lastEntityIdFromDb =
                    DatabaseHelper.GetLastEntityIdFromDb(entity.ReadAttributeValue(Tokens.TableName),
                                                         p.First().ReadAttributeValue(Tokens.PropertyName), DataAccess);
                if (lastEntityIdFromDb > 0)
                {
                    FileHelper.SaveToFile(entityKeyFilename, lastEntityIdFromDb.ToString());
                }
            }
        }

        private void DbInsertScriptForEntitiesWithDependencies(XElement entity, StringBuilder propertyNames,
                                                               StringBuilder propertyValues, string entityKeyFilename,
                                                               string patternName, XElement patternEntities,
                                                               int currentIteration)
        {
            string tableName = entity.ReadAttributeValue(Tokens.TableName);
            foreach (XElement property in entity.Descendants())
            {
                bool isEntitykey = Convert.ToBoolean(property.ReadAttributeValue(Tokens.Key));
                bool isForeignEntitykey = property.ContainsAttributeWithoutValue(Tokens.References, "");

                if (isEntitykey)
                {
                    EntityKeyGenerator.AppendEntitykeyPropertyValue(propertyValues, entityKeyFilename, property,
                                                                    patternName, patternEntities);
                }
                else if (isForeignEntitykey)
                {
                    string referenceValue;
                    if (property.ContainsAttributeWithoutValue(Tokens.ActualValue, ""))
                    {
                        referenceValue = property.ReadAttributeValue(Tokens.ActualValue);
                    }
                    else
                    {
                        string referenceEntity = property.ReadAttributeValue(Tokens.References);
                        XElement entityNode = patternEntities.RetrieveEntity(referenceEntity);
                        referenceValue =
                            FileHelper.ReadLastEntry(ParameterizedAppSettings.GenerateEntityKeyFilename(patternName,
                                                                                                        entityNode.
                                                                                                            ReadAttributeValue
                                                                                                            (Tokens.
                                                                                                                 TableName)));
                    }

                    propertyValues.AppendFormat("{0},", referenceValue);
                }
                else
                {
                    NonKeyColumnProcessor.AppendNonKeyPropertyValue(propertyValues, property, tableName,
                                                                    entityKeyFilename, DataAccess, currentIteration);
                }

                propertyNames.AppendFormat("{0},", property.ReadAttributeValue(Tokens.PropertyName));
            }


            propertyNames.Remove(propertyNames.Length - 1, 1);
            propertyValues.Remove(propertyValues.Length - 1, 1);
        }

        private void WriteEntityDbInsertScriptToFile(string tableName, string entityFilename,
                                                     StringBuilder propertyNames, StringBuilder propertyValues,
                                                     string entityCsvFilename, bool persistSql, bool generateCsv,
                                                     bool insertIntoDb, bool identityInsert,
                                                     SqlBatchManager sqlBatchManager)
        {
            string sqlStatement = string.Format("{3} INSERT INTO {0}({1}) VALUES({2}) {4}", tableName,
                                                propertyNames,
                                                propertyValues,
                                                DatabaseHelper.IndentityInsertOn(tableName, identityInsert),
                                                DatabaseHelper.IndentityInsertOff(tableName, identityInsert));

            if (persistSql)
            {
                FileHelper.SaveToFile(entityFilename, sqlStatement);
            }

            if (generateCsv)
            {
                FileHelper.SaveToFile(entityCsvFilename, propertyValues.ToString());
            }

            if (insertIntoDb)
            {
                // ExecuteCommand(sqlStatement);
                sqlBatchManager.AddSqlStatement(sqlStatement);
            }
        }

        private void DbInsertScriptForEntitiesWithoutDependencies(XContainer entity, StringBuilder propertyNames,
                                                                  StringBuilder propertyValues, string entityKeyFilename,
                                                                  string tableName, string patternName,
                                                                  XElement patternEntities, int currentIteration)
        {
            foreach (XElement property in entity.Descendants())
            {
                bool isEntitykey = Convert.ToBoolean(property.ReadAttributeValue(Tokens.Key));

                if (isEntitykey)
                {
                    EntityKeyGenerator.AppendEntitykeyPropertyValue(propertyValues, entityKeyFilename, property,
                                                                    patternName, patternEntities);
                }
                else
                {
                    NonKeyColumnProcessor.AppendNonKeyPropertyValue(propertyValues, property, tableName,
                                                                    entityKeyFilename, DataAccess, currentIteration);
                }

                propertyNames.AppendFormat("{0},", property.ReadAttributeValue(Tokens.PropertyName));
            }

            propertyNames.Remove(propertyNames.Length - 1, 1);
            propertyValues.Remove(propertyValues.Length - 1, 1);
        }
    }
}